﻿#region

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using BusinessObjects;

#endregion

namespace DataAccessLayer
{
    public static class ReportDAL
    {
        public static DataSet GetReportAboutHiredSubsidiary(int count, string order, int numberOfDays)
        {
            DataSet ds = new DataSet();
            using (
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("spGetReportAboutHiredSubsidiay", con);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@Count", count) {SqlDbType = SqlDbType.Int});
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@Order", order));
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@NumberOfDays", numberOfDays)
                {
                    SqlDbType = SqlDbType.Int
                });
                adapter.Fill(ds);
            }
            return ds;
        }

        public static DataSet GetReportAboutCustomerHire(int customerId)
        {
            DataSet ds = new DataSet();
            using (
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("spGetReportAboutCustomerHire", con);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@CustomerId", customerId)
                {
                    SqlDbType = SqlDbType.Int
                });
                adapter.Fill(ds);
            }
            return ds;
        }

        public static List<InactiveCustomer> GetReportAboutInActiveCustomerHire(int number)
        {
            DataSet ds = new DataSet();
            List<InactiveCustomer> customers = new List<InactiveCustomer>();
            using (
                SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString))
            {
                con.Open();
                SqlDataAdapter adapter = new SqlDataAdapter("spGetReportAboutInActiveCustomer", con);
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.Add(new SqlParameter("@Number", number) {SqlDbType = SqlDbType.Int});
                adapter.Fill(ds);
                DataTable dt = ds.Tables[0];
                foreach (DataRow dr in dt.Rows)
                {
                    SqlCommand cmd = new SqlCommand("spGetCustomerDetails", con)
                    {
                        CommandType = CommandType.StoredProcedure
                    };
                    cmd.Parameters.Add(new SqlParameter("@CustomerId", Convert.ToInt32(dr["CustomerId"]))
                    {
                        SqlDbType = SqlDbType.Int
                    });
                    SqlDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        customers.Add(new InactiveCustomer
                        {
                            Name = rdr["Name"].ToString(),
                            Type = rdr["Type"].ToString(),
                            EMail = rdr["EMail"].ToString(),
                            TelephoneNumber = rdr["TelephoneNo"].ToString(),
                            Subsidiary = rdr["Subsidiary"].ToString()
                        });
                    }
                    rdr.Close();
                }
            }
            return customers;
        }
    }
}